package com.shop.dao.impl;

import com.shop.dao.ShoppingCartDao;
import com.shop.entity.LookCartVO;
import com.shop.exception.DAOException;
import com.shop.transaction.TransactionManager;
import com.shop.util.ConnectionUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class ShoppingCartDaoImpl implements ShoppingCartDao {

    @Override
    public List<LookCartVO> lookCart(int u_id) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<LookCartVO> list = new ArrayList<>();

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "select " +
                    "s1.sc_id sc_id," +
                    "s1.sc_num sc_num," +
                    "s2.sd_id sd_id," +
                    "s2.sd_num sd_num," +
                    "s2.sd_state sd_state,"+
                    "g1.gd_id gd_id," +
                    "g1.gd_price gd_price," +
                    "g1.gd_num gd_num," +
                    "g2.g_id g_id," +
                    "g2.g_name g_name," +
                    "g1.s_id s_id," +
                    "sd_num*gd_price total " +
                    "from shoppingcart s1 " +
                    "join shopcart_detail s2 " +
                    "on s1.sc_id=s2.sc_id " +
                    "join good_detail g1 " +
                    "on s2.gd_id=g1.gd_id " +
                    "join goods g2 " +
                    "on g1.g_id=g2.g_id "+
                    "where s1.u_id=? ";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,u_id);
            rs = ps.executeQuery();
            while(rs.next()) {
                LookCartVO lc =
                        new LookCartVO(u_id,rs.getInt("sc_id")
                                ,rs.getInt("sc_num"),rs.getInt("sd_id"),
                                rs.getInt("gd_id"),rs.getInt("sd_num"),
                                rs.getDouble("gd_price"),rs.getInt("gd_num"),
                                rs.getInt("g_id"),rs.getString("g_name"),
                                rs.getInt("s_id"),rs.getDouble("total"));
                lc.setSd_state(rs.getInt("sd_state"));
                list.add(lc);
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return list;
    }

    @Override
    public int deleteGoodsById(int sc_id, int sd_id) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "delete from shopcart_detail where sc_id=? and sd_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sc_id);
            ps.setInt(2,sd_id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public int changeNum(int sc_id, int sd_id, int sd_num) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "update shopcart_detail set sd_num=? where sc_id=? and sd_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sd_num);
            ps.setInt(2,sc_id);
            ps.setInt(3,sd_id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public int changeStateSingle(int sc_id, int sd_id, int sd_state) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "update shopcart_detail set sd_state=? where sc_id=? and sd_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sd_state);
            ps.setInt(2,sc_id);
            ps.setInt(3,sd_id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public int changeState(int sc_id, int sd_id, int sd_state) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "update shopcart_detail set sd_state=? where sc_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sd_state);
            ps.setInt(2,sc_id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public double changeTotal(int sc_id, int sd_state) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        double result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "select " +
                    "s2.sd_num*g1.gd_price total " +
                    "from shoppingcart s1 join shopcart_detail s2 on s1.sc_id=s2.sc_id and s2.sd_state=?  join good_detail g1 on s2.gd_id=g1.gd_id " +
                    "where s1.sc_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sd_state);
            ps.setInt(2,sc_id);
            rs = ps.executeQuery();
            while(rs.next()) {
                result += rs.getDouble("total");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public int changeCartTotal(int sc_id, double total) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "update shoppingcart set sc_num=? where sc_id=?";
            ps = conn.prepareStatement(sql);
            ps.setDouble(1,total);
            ps.setInt(2,sc_id);
            result = ps.executeUpdate();

        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    //��ȡ���ﳵid
    @Override
    public int getCartId(int u_id) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "select sc_id from shoppingcart where u_id=?";
            ps = conn.prepareStatement(sql);
            ps.setDouble(1,u_id);
            rs = ps.executeQuery();
            if(rs.next()) {
                result = rs.getInt("sc_id");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    //ɾ�����ﳵ��״̬Ϊ 1 ����Ʒ
    @Override
    public void deleteGoodsByIdAndState(int sc_id, String s) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;
        int gd_id = Integer.parseInt(s);
        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "delete from shopcart_detail where sc_id=? and gd_id=? and sd_state=1";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sc_id);
            ps.setInt(2,gd_id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
    }

    @Override
    public String getFourById(int gd_id) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String result = null;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "select g2.gfour_name gfour_name " +
                    "from good_detail g1 join goods_four_type g2 on g1.gd_id=? and g1.gfour_id=g2.gfour_id";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,gd_id);
            rs = ps.executeQuery();
            if(rs.next()) {
                result = rs.getString("gfour_name");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public int getImgId(int gd_id) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "select img_id from img_path where gd_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,gd_id);
            rs = ps.executeQuery();
            if(rs.next()) {
                result = rs.getInt("img_id");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public String getGoodsImg(String gd_id) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        String result = null;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "select img_path from img_path where gd_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,Integer.parseInt(gd_id));
            rs = ps.executeQuery();
            if(rs.next()) {
                result = rs.getString("img_path");
            }
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public int clearCart(int sc_id) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "delete from shopcart_detail where sc_id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sc_id);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }

    @Override
    public int addCart(int sc_id, int gd_id, int sd_num, int sd_state) throws DAOException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int result = 0;

        try {
            conn = TransactionManager.getTransactionConn();
            String sql = "insert into shopcart_detail(sc_id,gd_id,sd_num,sd_state) values(?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,sc_id);
            ps.setInt(2,gd_id);
            ps.setInt(3,sd_num);
            ps.setInt(4,sd_state);
            result = ps.executeUpdate();
        } catch (SQLException e) {
            System.out.println(e.getMessage());
            e.printStackTrace();
            throw new DAOException(e);
        }finally {
            ConnectionUtils.close(rs,ps,null);
        }
        return result;
    }
}
